package itsm.isperp.module.service.sparepart;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.commons.repository.BaseRepository;
import itsm.isperp.framework.data.domain.PageResponse;
import itsm.isperp.framework.service.BaseService;
import itsm.isperp.framework.utils.HibernateUUIDGenerator;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.module.entity.sparepart.SptSparepartMinStockRule;
import itsm.isperp.module.entity.sparepart.SptSparepartPlan;
import itsm.isperp.module.repository.sparepart.SptSparepartMinStockRuleMapper;
import itsm.isperp.module.repository.sparepart.SptSparepartPlanMapper;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class SptSparepartPlanService extends BaseService<SptSparepartPlan> {

	@Autowired
	protected SptSparepartPlanMapper SptSparepartPlanMapper;

	@Autowired
	protected SptSparepartMinStockRuleMapper SptSparepartPlanMinStockRuleMapper;

	@Override
	public BaseRepository<SptSparepartPlan> getRepository() {
		return this.SptSparepartPlanMapper;
	}

	public PageResponse<Map<String, Object>> findAllDetailByRequest(
			JqGridRequest req) {
	  
		return this.SptSparepartPlanMapper.findAllDetailByRequest(req).toPage();
	}

	public void updateStatus(String sparepartId,String status) {
     String sql="update spt_sparepart_plan set status='"+status+"' where relation_id='"+sparepartId+"'";
     JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
     jdbcTemplate.execute(sql);
  }
	
	public void batchSaveSptSparepartPlanMinStockRule(String sparepartId,
			List<String> confCategoryIds) {

		String sql = "INSERT INTO spt_sparepart_min_stock_rule(id,spt_sparepart_id,conf_category_id)VALUES(?,?,?)";

		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		List<Object[]> list = new ArrayList<>();

		if (confCategoryIds != null && confCategoryIds.size() > 0) {
			for (String m : confCategoryIds) {
				Object[] arg = new Object[3];
				arg[0] = HibernateUUIDGenerator.generate();
				arg[1] = sparepartId;
				arg[2] = m;

				list.add(arg);
			}

			jdbcTemplate.batchUpdate(sql, list);
		}

	}

	public PageResponse<Map<String, Object>> findAllProjectUsedByRequest(
			String sparepartId, JqGridRequest req) {

		req.addFilterRule("spt_sparepart_id", JqGridFilterRuleOp.eq,
				sparepartId);
		return SptSparepartPlanMapper.findAllProjectUsedByRequest(req).toPage();
	}
	
	public PageResponse<Map<String, Object>> findSparepartAllList(
			JqGridRequest req) {
		return SptSparepartPlanMapper.findSparepartAllList(req).toPage();
	}

	/**
	 * 刷新库存
	 * 
	 * @return
	 */
	public ResultMessage updateStore() {
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		String[] clearSqls = new String[3];

		clearSqls[0] = "DELETE FROM spt_sparepart_in_detail "
				+ "WHERE spt_sparepart_in_id NOT IN (SELECT id FROM  spt_sparepart_in)";

		clearSqls[1] = "DELETE  FROM spt_sparepart_change_detail "
				+ " WHERE relation_id NOT IN "
				+ "  (SELECT  id   FROM spt_sparepart_out  UNION   ALL "
				+ "  SELECT   id    FROM    spt_sparepart_back "
				+ "  UNION  ALL  SELECT  id FROM spt_sparepart_discard)";
		clearSqls[2] = "UPDATE spt_sparepart SET in_stock_quantity=0,out_stock_quantity=0,back_stock_quantity=0,discard_quantity=0";

		jdbcTemplate.batchUpdate(clearSqls);

		String[] sqls = new String[4];

		sqls[0] = "UPDATE  "
				+ "  spt_sparepart a  "
				+ "  INNER JOIN  "
				+ "    (SELECT  "
				+ "      SUM(quantity) qc, "
				+ "      b.spt_sparepart_id  "
				+ "    FROM "
				+ "      spt_sparepart_in_detail b  "
				+ "      INNER JOIN spt_sparepart_in c  "
				+ "        ON b.spt_sparepart_in_id = c.id  "
				+ "    WHERE c.STATUS = '已关闭' GROUP BY spt_sparepart_id) t  "
				+ "    ON a.id = t.spt_sparepart_id SET in_stock_quantity = qc ";

		sqls[1] = "UPDATE  "
				+ "  spt_sparepart a  "
				+ "  INNER JOIN  "
				+ "    (SELECT  "
				+ "      SUM(quantity) qc, "
				+ "      b.spt_sparepart_id  "
				+ "    FROM "
				+ "      spt_sparepart_change_detail b  "
				+ "      INNER JOIN spt_sparepart_out c  "
				+ "        ON b.relation_id = c.id  "
				+ "    WHERE c.STATUS = '已关闭' GROUP BY spt_sparepart_id) t  "
				+ "    ON a.id = t.spt_sparepart_id SET out_stock_quantity = qc ";

		sqls[2] = "UPDATE  "
				+ "  spt_sparepart a  "
				+ "  INNER JOIN  "
				+ "    (SELECT  "
				+ "      SUM(quantity) qc, "
				+ "      b.spt_sparepart_id  "
				+ "    FROM "
				+ "      spt_sparepart_change_detail b  "
				+ "      INNER JOIN spt_sparepart_back c  "
				+ "        ON b.relation_id = c.id  "
				+ "    WHERE c.STATUS = '已关闭' GROUP BY spt_sparepart_id) t  "
				+ "    ON a.id = t.spt_sparepart_id SET back_stock_quantity = qc  ";

		sqls[3] = "    UPDATE  "
				+ "  spt_sparepart a  "
				+ "  INNER JOIN  "
				+ "    (SELECT  "
				+ "      SUM(quantity) qc, "
				+ "      b.spt_sparepart_id  "
				+ "    FROM "
				+ "      spt_sparepart_change_detail b  "
				+ "      INNER JOIN spt_sparepart_discard c  "
				+ "        ON b.relation_id = c.id  "
				+ "    WHERE c.STATUS = '已关闭' GROUP BY spt_sparepart_id) t  "
				+ "    ON a.id = t.spt_sparepart_id SET  discard_quantity = qc ";
		jdbcTemplate.batchUpdate(sqls);
		return ResultMessage.success();
	}
}